"""
Case Type   : gs_checkse工具功能
Case Name   : 验证gs_checkse的A1检测项确保用户的最大连接数配置正确检测与修复成功
Create At   : 2024/10/23
Owner       : lihongji
Description :
    1.创建用户gs_tester后检测A1项
    2.修复A1不符合安全规范项目后再次检测
    3.查看系统表PG_ROLES的gs_tester的rolconnlimit的值
    4.创建用户gs_tester1,并将gs_tester与gs_tester1的rolconnlimit分别改为800跟2000
    5.检测A1项后进行修复，再次检测
Expect      :
    1.创建用户成功，检测打印告警信息
    2.修复成功且不打印告警信息
    3.rolconnlimit为1024
    4.创建用户成功，更改rolconnlimit的值成功
    5.修复成功再次检测不打印告警信息
History     :
"""
import os
import unittest

from testcase.utils.Logger import Logger
from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from yat.test import Node
from yat.test import macro


class GS_CHECKSE(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'----{os.path.basename(__file__)} start----')
        self.primaryNode = Node('PrimaryDbUser')
        self.sh_primary = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.common = Common()
        self.env_path = macro.DB_ENV_PATH

    def test_gscheckse(self):
        step = ('----step1:创建用户gs_tester后检测A1项 '
                'expect:创建用户成功，检测打印告警信息----')
        self.log.info(step)
        create_sql = ("drop user if exists gs_tester;"
                      "create user gs_tester identified by 'qwer*963.';")
        result = self.sh_primary.execut_db_sql(create_sql)
        self.log.info(result)
        gs_checkse_cmd = (f'source {self.env_path};'
                          f'gs_checkse -i A1 --detail;')
        self.log.info(gs_checkse_cmd)
        check_res = self.common.get_sh_result(self.primaryNode, gs_checkse_cmd)
        self.log.info(check_res)
        self.assertIn("Ensure the maximum connection settings for users"
                      " are configured correctly", check_res, '执行失败' + step)
        self.assertIn(self.constant.CREATE_ROLE_SUCCESS_MSG,
                      result, '执行失败' + step)

        step = ('----step2:修复A1不符合安全规范项目后再次检测 '
                'expect:修复成功且不打印告警信息----')
        self.log.info(step)
        gs_checkse_cmd = (f'source {self.env_path};'
                          f'gs_checkse -i B1 --detail;'
                          f'gs_checkse -i A1 --detail;')
        self.log.info(gs_checkse_cmd)
        check_res = self.common.get_sh_result(self.primaryNode, gs_checkse_cmd)
        self.assertNotIn("Ensure the maximum connection settings for "
                         "users are configured correctly", check_res,
                         '执行失败' + step)

        step = ('----step3:查看系统表PG_ROLES的gs_tester的rolconnlimit的值 '
                'expect:rolconnlimit为1024----')
        self.log.info(step)
        check_sql = ("SELECT rolname, rolconnlimit FROM pg_roles WHERE "
                     "rolname NOT LIKE 'gs_role%';")
        check_res = self.sh_primary.execut_db_sql(check_sql)
        self.log.info(check_res)
        self.assertIn("1024", check_res, '执行失败' + step)

        step = ('----step4:创建用户gs_tester1,并将gs_tester与gs_tester1的'
                'rolconnlimit分别改为800跟2000 expect:创建用户、更改值成功----')
        self.log.info(step)
        create_sql = ("drop user if exists gs_tester1;"
                      "create user gs_tester1 identified by 'qwer*963.';"
                      "ALTER ROLE gs_tester1 CONNECTION LIMIT 800;"
                      "ALTER ROLE gs_tester CONNECTION LIMIT 2000;")
        result = self.sh_primary.execut_db_sql(create_sql)
        self.log.info(result),
        self.assertEqual(2, result.count(self.constant.ALTER_ROLE_SUCCESS_MSG),
                         '执行失败' + step)

        step = ('----step5:检测A1项后进行修复，再次检测  '
                'expect:修复成功再次检测不打印告警信息----')
        self.log.info(step)
        gs_checkse_cmd = (f'source {self.env_path};'
                          f'gs_checkse -i B1 --detail;'
                          f'gs_checkse -i A1 --detail;')
        self.log.info(gs_checkse_cmd)
        check_res = self.common.get_sh_result(self.primaryNode, gs_checkse_cmd)
        self.log.info(check_res)
        self.assertNotIn("Ensure the maximum connection settings for "
                         "users are configured correctly", check_res,
                         '执行失败' + step)

    def tearDown(self):
        step = '----step6:恢复环境    expect:成功----'
        self.log.info(step)
        drop_user = ("drop user if exists gs_tester;"
                     "drop user if exists gs_tester1;")
        result = self.sh_primary.execut_db_sql(drop_user)
        self.log.info(result)
        self.assertEqual(2, result.count(self.constant.DROP_ROLE_SUCCESS_MSG),
                         '执行失败' + step)
        self.log.info(f'----{os.path.basename(__file__)} end----')